package com.schoolvisitors.repository;

import com.schoolvisitors.entity.Reservation;
import com.schoolvisitors.enums.ReservationStatusEnum;
import com.schoolvisitors.enums.ReservationTypeEnum;
import jakarta.persistence.LockModeType;
import jakarta.validation.constraints.NotNull;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Lock;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Collection;
import java.util.List;
import java.util.Optional;

@Repository
public interface ReservationRepository extends JpaRepository<Reservation, Long> {

    List<Reservation> findByUserId(Long userId);

    Optional<Reservation> findByRoomIdAndCubicleNumberAndTimeSlotIdAndDate(@NotNull Long roomId, @NotNull Short cubicleNumber, @NotNull Long timeSlotId, @NotNull LocalDate date);

    // 根据状态获取所有未审核的预约
    List<Reservation> findByStatusAndReviewerId(ReservationStatusEnum status, Long reviewerId);


    Page<Reservation> findByUserId(Long userId, Pageable pageable);
    List<Reservation> findByRoomIdAndCubicleNumberAndDate(Long roomId, Short cubicleNumber, LocalDate date);

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT c FROM Reservation c WHERE c.id = :id")
    Optional<Reservation> findByIdForLock(@Param("id") Long id);

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT c FROM Reservation c WHERE c.type = :type and c.id = :id")
    Optional<Reservation> findByTypeAndIdForLock(@Param("type") ReservationTypeEnum type, @Param("id") Long id);


    // 获取指定用户指定隔间当前时段的预约
    Reservation findByUserIdAndRoomIdAndCubicleNumberAndDateAndTimeSlotIdAndStatus(Long userId, Long roomId, Short cubicleNumber, LocalDate date, Long timeSlotId, ReservationStatusEnum status);

    Optional<Reservation> findByRoomIdAndCubicleNumberAndTimeSlotIdAndDateAndStatus(Long roomId, Short cubicleNumber, Long roomId1, LocalDate date, ReservationStatusEnum status);


    Optional<Reservation> findByRoomIdAndCubicleNumberAndTimeSlotIdAndDateAndUserIdAndStatus(Long roomId, Short cubicleNumber, Long timeSlotId, LocalDate date, Long userId, ReservationStatusEnum status);

    @Query("SELECT cr FROM Reservation cr " +
            "JOIN FETCH cr.user u " +
            "JOIN FETCH cr.room r " +
            "JOIN FETCH cr.timeSlot ts " +
            "WHERE cr.reviewerId = :reviewerId")


    List<Reservation> findByTypeAndStatusAndReviewerId(ReservationTypeEnum type, ReservationStatusEnum status, Long reviewerId);


    List<Reservation> findByTypeAndInstrumentIdAndDate(ReservationTypeEnum type, Long instrumentId, LocalDate date);


    // 仪器

//    @Lock(LockModeType.PESSIMISTIC_WRITE)
//    @Query("SELECT r FROM Reservation r WHERE r.instrumentId = :instrumentId AND r.date = :date AND r.timeSlotId = :timeSlotId AND r.status = :status")
//    Optional<Reservation> findByTypeAndInstrumentIdAndDateAndTimeSlotIdAndStatusWithLock(
//            @Param("type") ReservationTypeEnum type,
//            @Param("instrumentId") Long instrumentId,
//            @Param("date") LocalDate date,
//            @Param("timeSlotId") Long timeSlotId,
//            @Param("status") ReservationStatusEnum status
//    );

    List<Reservation> findByTypeAndIdAndDate(ReservationTypeEnum type, Long id, LocalDate date);

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    Optional<Reservation> findByTypeAndInstrumentIdAndDateAndTimeSlotIdAndStatus(ReservationTypeEnum type, Long instrumentId, LocalDate date, Long timeSlotId, ReservationStatusEnum status);

    Optional<Reservation> findByTypeAndInstrumentIdAndDateAndTimeSlotIdAndUserIdAndStatus(ReservationTypeEnum type, @NotNull(message = "仪器ID不能为空") Long instrumentId, @NotNull(message = "预约日期不能为空") LocalDate date, @NotNull(message = "时间段ID不能为空") Long timeSlotId, Long userId, ReservationStatusEnum reservationStatusEnum);

    boolean existsByTypeAndInstrumentIdAndStatusAndDateGreaterThanEqual(ReservationTypeEnum type, Long instrumentId, ReservationStatusEnum reservationStatusEnum, LocalDate now);

    Page<Reservation> findByTypeAndUserId(ReservationTypeEnum type, Long userId, Pageable pageable);

    List<Reservation> findByTypeInAndRoomIdAndCubicleNumberAndDate(Collection<ReservationTypeEnum> types, Long roomId, Short cubicleNumber, LocalDate date);

    List<Reservation> findByStatusAndDateLessThanEqual(ReservationStatusEnum reservationStatusEnum, LocalDate now);


    // 今日预约会议室数（去重）
    @Query(value = "SELECT COUNT(DISTINCT cubicle_number) FROM reservations WHERE date = :date AND type = :type", nativeQuery = true)
    long countDistinctCubicleNumberByDateAndType(@Param("date") LocalDate date, @Param("type") String type);


    // 今日预约人数
    @Query(value = "SELECT COUNT(DISTINCT user_id) FROM reservations WHERE date = :date", nativeQuery = true)
    long countDistinctUserByDate(@Param("date") LocalDate date);

    Page<Reservation> findByTypeAndReviewerId(ReservationTypeEnum type, Long reviewerId, Pageable pageable);

    Page<Reservation> findByTypeAndReviewerIdAndStatus(ReservationTypeEnum type, Long reviewerId, ReservationStatusEnum status, Pageable pageable);

    List<Reservation> findByStatusAndNotOutWarnAndDateLessThanEqual(ReservationStatusEnum status, Boolean notOutWarn, LocalDate dateIsLessThan);

    List<Reservation> findByTypeAndCreateAtBetween(ReservationTypeEnum type, LocalDateTime createAtAfter, LocalDateTime createAtBefore);

    @Query("SELECT r FROM Reservation r " +
            "JOIN r.user u " +
            "WHERE r.type = :type " +
            "AND r.reviewerId = :reviewerId " +
            "AND u.realName LIKE %:search%")
    Page<Reservation> searchByTypeAndReviewerAndUserRealName(
            @Param("type") ReservationTypeEnum type,
            @Param("reviewerId") Long reviewerId,
            @Param("search") String search,
            Pageable pageable
    );

    Page<Reservation> findByNotOutWarnTrueAndReviewerId( Long reviewerId, Pageable pageable);

    @Query("SELECT r FROM Reservation r " +
            "JOIN r.user u " +
            "WHERE r.notOutWarn = true " +
            "AND r.reviewerId = :reviewerId " +
            "AND u.realName LIKE %:search%")
    Page<Reservation> searchByNotOutWarnTrueAndReviewerIdAndUserRealName(Long reviewerId, String search, Pageable pageable);
}
